﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DemoGrid
{
    public class EmployeeDAO
    {
        public IList<Employee> GetEmployeeDetails(int currentPageNo, int pageRecordsCount, out int totalRecordCount)
        {
            SqlConnection connection = null;
            SqlDataReader dataReader = null;

            try
            {
                IList<Employee> empList = new List<Employee>();
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

                SqlDataAdapter da = new SqlDataAdapter("SP_GET_EMP_DETAILS", connection);
                da.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

                da.SelectCommand.Parameters.Add(new SqlParameter("@CurrentPageNo", SqlDbType.Int));
                da.SelectCommand.Parameters["@CurrentPageNo"].Value = currentPageNo;

                da.SelectCommand.Parameters.Add(new SqlParameter("@PageRecordsCount", SqlDbType.Int));
                da.SelectCommand.Parameters["@PageRecordsCount"].Value = pageRecordsCount;
        
                da.SelectCommand.Parameters.Add(new SqlParameter("@TotalRecordCount", SqlDbType.Int));
                da.SelectCommand.Parameters["@TotalRecordCount"].Direction = ParameterDirection.Output;
                connection.Open();

                DataSet dataSet = new DataSet();
                da.Fill(dataSet);

                foreach (DataRow dataRow in dataSet.Tables[0].Rows)
                {
                    Employee empObj = new Employee();
                    empObj.ID = Convert.ToInt32(dataRow["Id"].ToString());
                    empObj.EmployeeName = dataRow["Name"].ToString();
                    empObj.Location = dataRow["Location"].ToString();
                    empObj.Department = dataRow["Department"].ToString();

                    empList.Add(empObj);
                }
                
                totalRecordCount = Convert.ToInt32(da.SelectCommand.Parameters["@TotalRecordCount"].Value);
                connection.Close();
                return empList;
            }
            catch (Exception ex)
            {
                if (connection != null) connection.Close();
                if (dataReader != null) dataReader.Close();                
                throw ex;
            }
        }
    }
}